﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DLM.Domain.DTO;

namespace DLM.Data.EF.CodeFirst.Repositories
{
    public class PageDataRepository
    {
        public IEnumerable<DLM.Domain.DTO.BillBoard> BillboardList()
        {
            return new DatabaseFactory().GetDataContext()
                .Database.SqlQuery<BillBoard>("select top 30 c.userid,c.accountname,c.name,c.photo,(cast(a.ParticipateCount as int) * 100 + cast(isnull(b.viewcount,0) as int)) rate " +
            " from (select count(up.partyid) as ParticipateCount,up.userid from UserPartyAssociation up join party p on up.partyid=p.partyid where p.Status=1 and p.issuccess=1 group by up.userid) as a left join " +
            "(select distinct count(ip) as viewcount,userid from userview group by userid) as b on a.userid = b.userid " +
            "join [user] c on a.userid=c.userid join account d on d.accountid=c.accountid where d.isvalidation=1 order by rate desc");
        }

        public IEnumerable<DLM.Domain.DTO.BillBoard> TopBillboardList()
        {
            return new DatabaseFactory().GetDataContext()
                          .Database.SqlQuery<BillBoard>("select top 8 c.userid,c.accountname,c.name,c.photo,(cast(a.ParticipateCount as int) * 100 + cast(isnull(b.viewcount,0) as int)) rate " +
                      " from (select count(up.partyid) as ParticipateCount,up.userid from UserPartyAssociation up join party p on up.partyid=p.partyid where p.Status=1 and p.issuccess=1 group by up.userid) as a left join " +
                      "(select distinct count(ip) as viewcount,userid from userview group by userid) as b on a.userid = b.userid " +
                      "join [user] c on a.userid=c.userid join account d on d.accountid=c.accountid where d.isvalidation=1 order by rate desc");
        }

        public IEnumerable<DLM.Domain.DTO.ProvincesDTO> FindCountry()
        {
            return new DatabaseFactory().GetDataContext()
                          .Database.SqlQuery<DLM.Domain.DTO.ProvincesDTO>("SELECT provinceid,provincename FROM province");
        }

        public IEnumerable<CityDTO> FindCity(string province)
        {
            return new DatabaseFactory().GetDataContext()
                          .Database.SqlQuery<DLM.Domain.DTO.CityDTO>("SELECT cityid,cityname FROM city c join province p on c.provinceid=p.provinceid WHERE provincename='" + province + " '");
        }

        public IEnumerable<DistrictDTO> FindDistrict(string city)
        {
            return new DatabaseFactory().GetDataContext()
                          .Database.SqlQuery<DLM.Domain.DTO.DistrictDTO>("SELECT districtid,districtname FROM District d join city c on c.cityid=d.cityid WHERE cityname='" + city + "' ");
        }

        public int FindFriendRequestCount(string accountName)
        {
            if (string.IsNullOrEmpty(accountName))
            {
                throw new ArgumentNullException("Account name");
            }
            return new DatabaseFactory().GetDataContext()
                .Database.SqlQuery<int>(string.Format("select count(*) from friend f join [user] u on u.userid=f.myfriendid join [user] d on d.userid = f.userid where u.accountname='{0}' and isconfirmed=0", accountName)).FirstOrDefault();
        }


        public int FindGreetRequestCount(string accountName)
        {
            if (string.IsNullOrEmpty(accountName))
            {
                throw new ArgumentNullException("Account name");
            }
            return new DatabaseFactory().GetDataContext()
                .Database.SqlQuery<int>(string.Format("select count(*) from salutation s join [user] u on s.greetid = u.userid where u.accountname='{0}' and s.isconfirm= 0 ", accountName)).FirstOrDefault();
        }


        public int FindPartyRequestCount(string accountName)
        {
            if (string.IsNullOrEmpty(accountName))
            {
                throw new ArgumentNullException("Account name");
            }
            return new DatabaseFactory().GetDataContext()
                .Database.SqlQuery<int>(string.Format("select count(*) from invitation i join [user] u on i.userid = u.userid where u.accountname='{0}' and i.isconfirmed=0", accountName)).FirstOrDefault();
        }
    }
}
